﻿using CodeGenerator.DBInfo.entity;
using CodeGenerator.Utils;
using MySql.Data.MySqlClient;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeGenerator.DBInfo.Impl
{
    /// <summary>
    /// PostgreSQL数据库信息获取类
    /// </summary>
    internal class PostgreSQLDBInfoGetter : AbstractDbInfoGetter<NpgsqlConnection, NpgsqlDataAdapter>, IDBInfoGetter
    {
        //数据量类型与Java类型映射
        readonly Dictionary<string, string> TypeMappings = new Dictionary<string, string>
            {
                { "SMALLINT", "Short" },
                { "INTEGER", "Integer" },
                { "BIGINT", "Long" },
                { "INT2", "Short" },
                { "INT4", "Integer" },
                { "INT8", "Long" },
                { "DECIMAL", "Double" },
                { "NUMERIC", "Long" },
                { "REAL", "Float" },
                { "DOUBLE PRECISION", "Double" },
                { "SMALLSERIAL", "Short" },
                { "SERIAL", "Integer" },
                { "BIGSERIAL", "Long" },
                { "MONEY", "BigDecimal" },
                { "CHARACTER VARYING", "String" },
                { "VARCHAR", "String" },
                { "CHARACTER", "String" },
                { "CHAR", "String" },
                { "TEXT", "String" },
                { "BYTEA", "byte[]" },
                { "DATE", "LocalDateTime" },
                { "TIME", "LocalDateTime" },
                { "TIMETZ", "LocalDateTime" },
                { "TIMESTAMP", "LocalDateTime" },
                { "TIMESTAMPTZ", "LocalDateTime" },
                { "INTERVAL", "Duration" },
                { "BOOLEAN", "Boolean" },
                { "BOOL", "Boolean" },
                { "POINT", "PGpoint" },
                { "LINE", "PGline" },
                { "LSEG", "PGlseg" },
                { "BOX", "PGbox" },
                { "PATH", "PGpath" },
                { "POLYGON", "PGpolygon" },
                { "CIRCLE", "PGcircle" },
                { "UUID", "String" },
                { "JSON", "String" },
                { "JSONB", "String" },
                { "ARRAY", "List" },
                { "HSTORE", "Map" }
            };
        public PostgreSQLDBInfoGetter(ConnectionInfo connInfo) : base(connInfo)
        {
        }

        public override NpgsqlConnection Connect()
        {
            NpgsqlConnectionStringBuilder builder = new NpgsqlConnectionStringBuilder();
            builder.Username = ConnInfo.UserName;
            builder.Password = ConnInfo.Password;
            builder.Host = ConnInfo.Host;
            builder.Port = (int)ConnInfo.Port;
            builder.Database = ConnInfo.InitDb;
            return new NpgsqlConnection(builder.ConnectionString);
        }

        public override NpgsqlDataAdapter GetDataAdapter(string sql, IDbConnection conn)
        {
            return new NpgsqlDataAdapter(sql, conn as NpgsqlConnection);
        }

        public override List<string> GetDbList()
        {
            List<string> list = new List<string>();
            var dt = Query("select datname from pg_database where datistemplate = false");
            foreach (DataRow row in dt.Rows)
            {
                list.Add(row["datname"].ToString());
            }
            return list;
        }

        public override string GetJavaType(ColumnInfo column)
        {
            var columnType = column.Type.ToUpper();
            if (column.NumScale > 0 && columnType == "NUMERIC")
            {
                return "Double";
            }
            return TypeMappings.TryGetValue(columnType, out var type) ? type.ToString() : "String";
        }
        public override string GetJavaScriptType(ColumnInfo column)
        {
            var javaType = GetJavaType(column).ToLower();
            var numberTypes = new string[] { "double", "integer", "float", "short" };
            if (numberTypes.Contains(javaType))
            {
                return "number";
            }
            else if (javaType == "boolean")
            {
                return "boolean";
            }
            return "string";
        }
        public override List<string> GetSchemaList(string dbName)
        {
            List<string> list = new List<string>();
            var dt = Query("select schema_name from information_schema.schemata where schema_name not like 'pg\\_%' ESCAPE '\\' order by schema_name");
            foreach (DataRow row in dt.Rows)
            {
                list.Add(row["schema_name"].ToString());
            }
            return list;
        }

        public override TableInfo GetTableInfo(string db, string schema, string table)
        {
            var tbInfo = GetTableList(schema).Single(x => x.Name == table);
            if (tbInfo == null)
            {
                throw new Exception($"表 {table} 不存在！");
            }
            string tableComment = tbInfo.Comment;
            #region 字段查询
            var dt = Query($@"select
			                    c.column_name as COLUMN_NAME,
			                    c.udt_name as DATA_TYPE,
			                    coalesce ( c.character_maximum_length, numeric_precision_radix) as DATA_LENGTH,
			                    c.numeric_precision as DATA_PRECISION,
			                    c.numeric_scale as DATA_SCALE,
			                    SUBSTR (is_nullable,1,1) as nullable,
			                    d.description as comments,
			                    column_default as DATA_DEFAULT,
			                    now () as LAST_ANALYZED,
			                    ordinal_position as COLUMN_ID,
			                    case when (
				                    SELECT 
				                    count(*)
			                    FROM
			                      pg_constraint
			                      INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.
			                      OID INNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.OID 
			                      AND pg_attribute.attnum = ANY ( pg_constraint.conkey )
			                      INNER JOIN pg_type ON pg_type.OID = pg_attribute.atttypid 
			                     WHERE
			                      pg_class.relname = c.table_name
				                    AND pg_constraint.contype = 'p' and pg_attribute.attname = COLUMN_NAME
			                    )>0 then 1 else 0 end as is_pk
		                    from
			                    information_schema.columns c
		                    left join (
				                    select
					                    a.relname,
					                    b.objsubid,
					                    b.description
				                    from
					                    pg_class a,
					                    pg_description b
				                    where
					                    a.oid = b.objoid
			                    ) d
		                      on
			                    d.relname = c.table_name
			                    and d.objsubid = c.ordinal_position
		                    where
			                    c.table_schema = '{schema}'
			                    and c.table_name = '{table}'
		                    order by
			                    c.ordinal_position;
                    ");
            #endregion
            var columns = new List<ColumnInfo>();
            var primaryKeyCount = 0;
            foreach (DataRow row in dt.Rows)
            {
                string precision = row["data_precision"].ToString();
                string scale = row["data_scale"].ToString();
                var isPk = row["is_pk"].ToString() == "1";
                if (isPk) primaryKeyCount++;
                columns.Add(new ColumnInfo()
                {
                    Name = row["column_name"].ToString(),
                    Type = row["data_type"].ToString(),
                    IsPrimaryKey = isPk,
                    IsAllowNull = row["nullable"].ToString() == "Y",
                    Comment = row["comments"].ToString(),
                    NumLength = precision.IsNullOrEmpty() ? -1 : int.Parse(precision.ToString()),
                    NumScale = scale.IsNullOrEmpty() ? -1 : int.Parse(scale.ToString())
                });
            }
            return new TableInfo()
            {
                Name = table,
                TrimPrefixName = table,
                Comment = tableComment,
                Columns = columns,
                PrimaryKeyCount = primaryKeyCount
            };
        }

        public override List<dynamic> GetTableList(string parentName)
        {
            var list = new List<dynamic>();
            var dt = Query($@"SELECT
                                    t.tablename AS table_name,
                                    pgd.description AS table_comment
                                FROM
                                    pg_tables t
                                    LEFT JOIN pg_class c ON t.tablename = c.relname
                                    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
                                    LEFT JOIN pg_description pgd ON pgd.objoid = c.oid AND pgd.objsubid = 0
                                WHERE
                                    t.schemaname = '{parentName}'
                                union
                                select viewname AS table_name,
                                '' AS table_comment from pg_views 
                                where schemaname='{parentName}'
                                ORDER by table_name");

            foreach (DataRow row in dt.Rows)
            {
                list.Add(new { Name = row["table_name"].ToString(), Comment = row["table_comment"].ToString() });
            }
            return list;
        }

        public override void TestConnection()
        {
            var conn = Connect();
            conn.Open();
            conn.Close();
        }
    }
}
